﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using File = System.IO.File;

namespace DBD.Common.NPOI
{
    public static class ExcelReference
    {
        /// <summary>
        /// 修改Excel 一行
        /// </summary>
        /// <param name="rowIndex">行号</param>
        /// <param name="outputFile"></param>
        /// <param name="updateData"></param>
        public static void UpdateRow(int rowIndex, string outputFile, List<string> updateData)
        {
            try
            {
                using (FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook hssfworkbook;
                    if (outputFile.IndexOf(".xlsx") > 0) // 2007版本
                        hssfworkbook = new XSSFWorkbook(readfile);
                    else if (outputFile.IndexOf(".xls") > 0) // 2003版本
                        hssfworkbook = new HSSFWorkbook(readfile);
                    else
                        throw new Exception("文件后缀异常");

                    ISheet sheet1 = hssfworkbook.GetSheetAt(0);

                    for (int i = 0; i < updateData.Count; i++)
                    {
                        if (sheet1.GetRow(rowIndex) == null)
                        {
                            sheet1.CreateRow(rowIndex);
                        }
                        if (sheet1.GetRow(rowIndex).GetCell(i) == null)
                        {
                            sheet1.GetRow(rowIndex).CreateCell(i);
                        }

                        sheet1.GetRow(rowIndex).GetCell(i).SetCellValue(updateData[i]);
                    }

                    readfile.Close();

                    FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
                    hssfworkbook.Write(writefile);
                    writefile.Close();
                }

            }
            catch (Exception ex)
            {
                throw new Exception("UpdateRow 异常 - " + ex.Message);
            }
        }


        /// <summary>
        /// 创建Excel 一行
        /// </summary>
        /// <param name="outputFile"></param>
        /// <param name="updateData"></param>
        public static void CreateRow(string outputFile, List<string> updateData)
        {
            try
            {
                using (FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook hssfworkbook;
                    if (outputFile.IndexOf(".xlsx") > 0) // 2007版本
                        hssfworkbook = new XSSFWorkbook(readfile);
                    else if (outputFile.IndexOf(".xls") > 0) // 2003版本
                        hssfworkbook = new HSSFWorkbook(readfile);
                    else
                        throw new Exception("文件后缀异常");

                    ISheet sheet1 = hssfworkbook.GetSheetAt(0);

                    sheet1.CreateRow(sheet1.LastRowNum + 1);

                    for (int i = 0; i < updateData.Count; i++)
                    {
                        sheet1.GetRow(sheet1.LastRowNum).CreateCell(i);

                        sheet1.GetRow(sheet1.LastRowNum).GetCell(i).SetCellValue(updateData[i]);
                    }

                    readfile.Close();

                    FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
                    hssfworkbook.Write(writefile);
                    writefile.Close();
                }

            }
            catch (Exception ex)
            {
                throw new Exception("CreateRow 异常 - " + outputFile + ex.Message);
            }
        }


        #region 更新excel中的数据
        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, string sheetname, string[] updateData, int coluid, int rowid)
        {

            IWorkbook hssfworkbook = null;

            using (FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read))
            {


                //FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

                if (outputFile.IndexOf(".xlsx") > 0) // 2007版本
                    hssfworkbook = new XSSFWorkbook(readfile);
                else if (outputFile.IndexOf(".xls") > 0) // 2003版本
                    hssfworkbook = new HSSFWorkbook(readfile);

                //XSSFWorkbook hssfworkbook = new XSSFWorkbook(readfile);
                ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
                for (int i = 0; i < updateData.Length; i++)
                {
                    try
                    {
                        if (sheet1.GetRow(i + rowid) == null)
                        {
                            sheet1.CreateRow(i + rowid);
                        }
                        if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
                        {
                            sheet1.GetRow(i + rowid).CreateCell(coluid);
                        }

                        sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
                    }
                    catch (Exception ex)
                    {
                        // wl.WriteLogs(ex.ToString());
                        throw;
                    }
                }
                try
                {
                    readfile.Close();
                    FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
                    hssfworkbook.Write(writefile);
                    writefile.Close();
                }
                catch (Exception ex)
                {
                    // wl.WriteLogs(ex.ToString());
                }

            }
        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluids">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            readfile.Close();
            ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
            for (int j = 0; j < coluids.Length; j++)
            {
                for (int i = 0; i < updateData[j].Length; i++)
                {
                    try
                    {
                        if (sheet1.GetRow(i + rowid) == null)
                        {
                            sheet1.CreateRow(i + rowid);
                        }
                        if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
                        {
                            sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
                        }
                        sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
                    }
                    catch (Exception ex)
                    {
                        // wl.WriteLogs(ex.ToString());
                    }
                }
            }
            try
            {
                FileStream writefile = new FileStream(outputFile, FileMode.Create);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //wl.WriteLogs(ex.ToString());
            }
        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
            for (int i = 0; i < updateData.Length; i++)
            {
                try
                {
                    if (sheet1.GetRow(i + rowid) == null)
                    {
                        sheet1.CreateRow(i + rowid);
                    }
                    if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
                    {
                        sheet1.GetRow(i + rowid).CreateCell(coluid);
                    }

                    sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
                }
                catch (Exception ex)
                {
                    //wl.WriteLogs(ex.ToString());
                    throw;
                }
            }
            try
            {
                readfile.Close();
                FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //wl.WriteLogs(ex.ToString());
            }

        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluids">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            readfile.Close();
            ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
            for (int j = 0; j < coluids.Length; j++)
            {
                for (int i = 0; i < updateData[j].Length; i++)
                {
                    try
                    {
                        if (sheet1.GetRow(i + rowid) == null)
                        {
                            sheet1.CreateRow(i + rowid);
                        }
                        if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
                        {
                            sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
                        }
                        sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
                    }
                    catch (Exception ex)
                    {
                        //wl.WriteLogs(ex.ToString());
                    }
                }
            }
            try
            {
                FileStream writefile = new FileStream(outputFile, FileMode.Create);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //wl.WriteLogs(ex.ToString());
            }
        }

        #endregion




        // http://www.cnblogs.com/luxiaoxun/p/3374992.html




        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(string fileName, DataTable data, string sheetName, bool isColumnWritten)
        {
            IWorkbook workbook = null;
            FileStream fs = null;


            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;

            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (fileName.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();

            if (workbook != null)
            {
                sheet = workbook.CreateSheet(sheetName);
            }
            else
            {
                return -1;
            }

            if (isColumnWritten == true) //写入DataTable的列名
            {
                IRow row = sheet.CreateRow(0);
                for (j = 0; j < data.Columns.Count; ++j)
                {
                    row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                }
                count = 1;
            }
            else
            {
                count = 0;
            }

            for (i = 0; i < data.Rows.Count; ++i)
            {
                IRow row = sheet.CreateRow(count);
                for (j = 0; j < data.Columns.Count; ++j)
                {
                    row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                }
                ++count;
            }
            workbook.Write(fs); //写入到excel
            return count;

        }


        public static void CreaTest(string filePath)
        {
            IWorkbook workbook = null;
            using (FileStream fileStream = File.Open(filePath, FileMode.Open, FileAccess.Read))
            {
                if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fileStream);
                else if (filePath.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fileStream);

                ISheet sheet = workbook.GetSheet("Sheet1");
                //在第二行创建行    
                IRow row = sheet.CreateRow(0);
                //在第二行的第一列创建单元格    
                ICell cell = row.CreateCell(0);

                sheet.GetRow(0).GetCell(0).SetCellValue("编辑的值");

                workbook.Write(fileStream);
                fileStream.Close();
            }

        }


        public static void CreateExcel(string path, string name, DataTable table)
        {
            name = name + ".xls";
            HSSFWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet(name);

            for (int i = 0; i < table.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i);
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                }
            }

            // 写入到客户端  
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            using (FileStream fs = new FileStream(path + name, FileMode.Create, FileAccess.Write))
            {
                book.Write(fs);
            }

            ms.Close();
            ms.Dispose();
        }


        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string fileName, bool isFirstRowColumn, string sheetName = null)
        {
            IWorkbook workbook = null;

            DataTable data = new DataTable();
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                ISheet sheet = null;
                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    var startRow = 0;
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                throw;
                //Console.WriteLine("Exception: " + ex.Message);
                //return null;
            }
        }


        public static DataSet ReadExcel(string fileName, int startRow, string sheetName, params NpoiDataType[] columnDataType)
        {
            var ertime = 0;
            var intime = 0;
            var ds = new DataSet("ds");
            var dt = new DataTable("dt");
            var sb = new StringBuilder();
            using (var stream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = WorkbookFactory.Create(stream);//使用接口，自动识别excel2003/2007格式 
                ISheet sheet = workbook.GetSheet(sheetName);//得到里面第一个sheet 
                if (!string.IsNullOrEmpty(sheetName) && sheet.SheetName.ToLower() != sheetName)
                {
                    throw new Exception("导入的数据模板格式不正确,请重新导入!");
                }
                int j;
                IRow row;
                #region ColumnDataType赋值
                if (columnDataType.Length <= 0)
                {
                    row = sheet.GetRow(startRow - 1);//得到第i行 
                    columnDataType = new NpoiDataType[row.LastCellNum];
                    for (var i = 0; i < row.LastCellNum; i++)
                    {
                        var hs = row.GetCell(i);
                        columnDataType[i] = GetCellDataType(hs);
                    }
                }
                #endregion
                for (j = 0; j < columnDataType.Length; j++)
                {
                    var tp = GetDataTableType(columnDataType[j]);
                    dt.Columns.Add("c" + j, tp);
                }
                for (int i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++)
                {

                    row = sheet.GetRow(i);//得到第i行 
                    try
                    {
                        DataRow dr = dt.NewRow();
                        if (row != null)
                        {
                            for (j = 0; j < columnDataType.Length; j++)
                            {
                                dr["c" + j] = GetCellData(columnDataType[j], row, j);
                            }
                            bool allNull = true;
                            for (int k = 0; k < dr.ItemArray.Length; k++)
                            {
                                if (dr[k] != DBNull.Value && dr[k].ToString() != "")
                                {
                                    allNull = false;
                                    break;
                                }

                            }
                            if (!allNull)
                            {
                                dt.Rows.Add(dr);
                            }
                        }
                        intime++;
                    }
                    catch (Exception er)
                    {
                        ertime++;
                        sb.Append(string.Format("第{0}行出错：{1}\r\n", i + 1, er.Message));
                        continue;
                    }
                }
                ds.Tables.Add(dt);
            }
            if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
            return ds;
        }

        #region 读Excel-得到不同数据类型单元格的数据
        /// <summary> 
        /// 读Excel-得到不同数据类型单元格的数据 
        /// </summary> 
        /// <param name="datatype">数据类型</param> 
        /// <param name="row">数据中的一行</param> 
        /// <param name="column">哪列</param> 
        /// <returns></returns> 
        private static object GetCellData(NpoiDataType datatype, IRow row, int column)
        {
            if (row.GetCell(column) == null)
            {
                return DBNull.Value;
            }
            switch (datatype)
            {
                case NpoiDataType.String:
                    try
                    {
                        return row.GetCell(column).StringCellValue;
                    }
                    catch
                    {
                        try
                        {
                            return row.GetCell(column).StringCellValue;
                        }
                        catch
                        {
                            try
                            {
                                return row.GetCell(column).NumericCellValue;
                            }
                            catch
                            {
                                return "";
                            }
                        }
                    }
                case NpoiDataType.DateTimeString:
                    try
                    {
                        var data = row.GetCell(column);
                        if (data.CellType == CellType.NUMERIC)
                        {
                            return data.DateCellValue;
                        }
                        else
                        {
                            var str = data.StringCellValue;
                            DateTime dt = DateTime.Now;
                            bool flag = DateTime.TryParse(str, out dt);
                            if (flag)
                                return dt;
                            else
                                return null;
                        }
                    }
                    catch
                    {
                        return null;
                    }
                case NpoiDataType.Bool:
                    try { return row.GetCell(column).BooleanCellValue; }
                    catch
                    {
                        return row.GetCell(column).StringCellValue;
                    }
                case NpoiDataType.Datetime:
                    try { return row.GetCell(column).DateCellValue; }
                    catch
                    {
                        return row.GetCell(column).StringCellValue;
                    }
                case NpoiDataType.Numeric:
                    try { return row.GetCell(column).ToString(); }
                    catch
                    {
                        return row.GetCell(column).StringCellValue;
                    }
                case NpoiDataType.Richtext:
                    try { return row.GetCell(column).RichStringCellValue; }
                    catch
                    {
                        return row.GetCell(column).StringCellValue;
                    }
                case NpoiDataType.Error:
                    try { return row.GetCell(column).ErrorCellValue; }
                    catch
                    {
                        return row.GetCell(column).StringCellValue;
                    }
                case NpoiDataType.Blank:
                    try { return row.GetCell(column).StringCellValue; }
                    catch
                    {
                        return "";
                    }
                default: return "";
            }
        }
        #endregion

        #region 读Excel-根据NpoiDataType创建的DataTable列的数据类型
        /// <summary> 
        /// 读Excel-根据NpoiDataType创建的DataTable列的数据类型 
        /// </summary> 
        /// <param name="datatype"></param> 
        /// <returns></returns> 
        private static Type GetDataTableType(NpoiDataType datatype)
        {
            Type tp = typeof(string);//Type.GetType("System.String") 
            return tp;
            switch (datatype)
            {
                case NpoiDataType.Bool:
                    tp = typeof(bool);
                    break;
                case NpoiDataType.Datetime:
                    tp = typeof(DateTime);
                    break;
                case NpoiDataType.Numeric:
                    tp = typeof(double);
                    break;
                case NpoiDataType.Error:
                    tp = typeof(string);
                    break;
                case NpoiDataType.Blank:
                    tp = typeof(string);
                    break;
            }
            return tp;
        }
        #endregion

        #region 获取单元格数据类型
        /// <summary> 
        /// 获取单元格数据类型 
        /// </summary> 
        /// <param name="hs"></param> 
        /// <returns></returns> 
        private static NpoiDataType GetCellDataType(ICell hs)
        {
            NpoiDataType dtype;
            DateTime t1;
            string cellvalue = "";

            //if (hs == null) return NpoiDataType.String;
            return NpoiDataType.String;

            switch (hs.CellType)
            {
                case CellType.BLANK:
                    dtype = NpoiDataType.String;
                    cellvalue = hs.StringCellValue;
                    break;
                case CellType.BOOLEAN:
                    dtype = NpoiDataType.Bool;
                    break;
                case CellType.NUMERIC:
                    dtype = NpoiDataType.Numeric;
                    cellvalue = hs.NumericCellValue.ToString();
                    break;
                case CellType.STRING:
                    dtype = NpoiDataType.String;
                    cellvalue = hs.StringCellValue;
                    break;
                case CellType.ERROR:
                    dtype = NpoiDataType.Error;
                    break;
                case CellType.FORMULA:
                default:
                    dtype = NpoiDataType.Datetime;
                    break;
            }
            if (cellvalue != "" && DateTime.TryParse(cellvalue, out t1)) dtype = NpoiDataType.Datetime;
            return dtype;
        }
        #endregion
    }


    /// <summary> 
    /// 枚举(Excel单元格数据类型) 
    /// </summary> 
    public enum NpoiDataType
    {
        /// <summary> 
        /// 字符串类型-值为1 
        /// </summary> 
        String,
        /// <summary> 
        /// 布尔类型-值为2 
        /// </summary> 
        Bool,
        /// <summary> 
        /// 时间类型-值为3 
        /// </summary> 
        Datetime,
        /// <summary> 
        /// 数字类型-值为4 
        /// </summary> 
        Numeric,
        /// <summary> 
        /// 复杂文本类型-值为5 
        /// </summary> 
        Richtext,
        /// <summary> 
        /// 空白 
        /// </summary> 
        Blank,
        /// <summary> 
        /// 错误 
        /// </summary> 
        Error,
        /// <summary>
        /// 可空的时间类型
        /// </summary>
        DateTimeString
    }
}
